﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

/// <summary>
/// Summary description for WebDBManager
/// </summary>
public class WebDBManager
{
    #region VARIABLE_DECLARATION

    ////private static string DATASOURCE = "UNISOFT\\USLDB";
    ////private static string DATABASE = "UniLubs";
    ////private static string USERID = "sa";
    ////private static string USERNAME = "sa";
    ////private static string PASSWORD = "uslsa^&*12";

    private static string DATASOURCE = "SSELPC-1-PC";
    private static string DATABASE = "EmpireCare";
    private static string USERID = "sa";
    private static string USERNAME = "sa";
    private static string PASSWORD = "ssel";

    //private static string DATASOURCE = ".\\SQLEXPRESS";
    //private static string DATABASE = "UniLubs";
    //private static string USERID = "sa";
    //private static string USERNAME = "sa";
    //private static string PASSWORD = "mithusa";

   // private static bool ISSQLCONNECTION;
    private static string CONNECTIONSTRING;

    private static bool ISNEWCONNECTION;
    //public OleDbConnection iConnection = new OleDbConnection();
    //m
    //public SqlConnection jConnection = new SqlConnection();
    //m

    private static SqlDataReader iReader;

    private static readonly DataSet iDataSet = new DataSet();

    private static DataTable iDataTable = new DataTable();
    private readonly SqlCommand iCommand = new SqlCommand();

    private string errormessage = "";
    public SqlConnection iConnection = new SqlConnection();


    public string getDataSource()
    {
        return DATASOURCE;
    }

    public void setDataSource(string dataSource)
    {
        DATASOURCE = dataSource;
    }

    public string getDatabase()
    {
        return DATABASE;
    }

    public void setDatabase(string database)
    {
        DATABASE = database;
    }

    public string getUserId()
    {
        return USERID;
    }

    public void setUserId(string userId)
    {
        USERID = userId;
    }

    public string getUserName()
    {
        return USERNAME;
    }

    public void setUserName(string userName)
    {
        USERNAME = userName;
    }

    public string getPassword()
    {
        return PASSWORD;
    }

    public void setPassword(string password)
    {
        PASSWORD = password;
    }

    public string getConnectionString()
    {
        return ConfigurationManager.ConnectionStrings["EmpireCareConnectionString"].ConnectionString;
    }

    public string getConnectionStringMidleDB()
    {
        return ConfigurationManager.ConnectionStrings["EmpireCareConnectionString"].ConnectionString;
    }


    public void setConnectionString(string connectionString)
    {
        CONNECTIONSTRING = connectionString;
    }

    #endregion

    public WebDBManager()
    {
        CONNECTIONSTRING = ConfigurationManager.ConnectionStrings["EmpireCareConnectionString"].ConnectionString;
        //m
        // CONNECTIONSTRING = System.Configuration.ConfigurationManager.ConnectionStrings["UniaccconnectionString"].ConnectionString;
        //m
    }

    private string ConnectionString()
    {
        string iConnectionString = "";
        //string jConnectionString = "";

        iConnectionString = ConfigurationManager.ConnectionStrings["EmpireCareConnectionString"].ConnectionString;
        return iConnectionString;
        //m{
        //jConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["UniaccconnectionString"].ConnectionString;
        ////// return iConnectionString,jConnectionString;
        //return jConnectionString;
        // //m
    }

    private void Connect()
    {
        try
        {
            if (ISNEWCONNECTION)
            {
                CloseConnection();
                iConnection.ConnectionString = ConnectionString();
                iConnection.Open();
                ISNEWCONNECTION = false;
            }

            else
            {
                if (iConnection.State.ToString() == "Closed")
                {
                    iConnection.ConnectionString = ConnectionString();
                    iConnection.Open();
                }
            }
        }
        catch (Exception errExp)
        {
            //MessageBox.Show(e.Message + "\n\n Connection Failed");
        }
    }

    public double GetFieldCount()
    {
        return iReader.FieldCount;
    }

    private bool IsColumnExists(string columnname)
    {
        bool Exists = false;

        double NoOfFields = GetFieldCount();

        int i = 0;

        for (i = 0; i < NoOfFields; i++)
        {
            if (iReader.GetName(i).ToUpper() == columnname.ToUpper())
            {
                Exists = true;
                break;
            }
        }
        return Exists;
    }

    public string GetFieldValue(int id)
    {
        return FieldValue(id, "");
    }

    public string GetFieldValue(string fieldname)
    {
        return FieldValue(-1, fieldname);
    }

    private string FieldValue(int id, string fieldname)
    {
        try
        {
            string strValue = "";

            if (id != -1)
            {
                strValue = iReader.GetValue(id) + "";
            }

            else
            {
                if (IsColumnExists(fieldname))
                {
                    strValue = iReader[fieldname] + "";
                }
            }

            return strValue;
        }
        catch
        {
            throw;
        }
    }

    private void CloseConnection()
    {
        if (iConnection.State.ToString() != "Closed")
        {
            iConnection.Close();

            iConnection.Dispose();
        }
    }

    private void Command_Initialize(string sql)
    {
        Connect();

        iCommand.Dispose();

        iCommand.CommandTimeout = 0;

        iCommand.Connection = iConnection;

        iCommand.CommandText = sql;

        //iCommand.Transaction = iTransaction;

        //LastExecutedSqlString = sql.ToString();
    }

    private void ReadReader(string sql)
    {
        try
        {
            if (iReader != null)
            {
                iReader.Close();
            }

            Command_Initialize(sql);

            iReader = iCommand.ExecuteReader();


            iReader.Read();

            errormessage = "";
        }

        catch (Exception iException)
        {
            throw;
        }
    }

    public void ExecuteStoredProcedure(string Procedure)
    {
        string sql = "";
        sql = "EXEC " + Procedure;
        ReadReader(sql);
    }

    public bool Delete(string sql)
    {
        SqlConnection con = null;
        SqlTransaction tran = null;
        try
        {
            var sa = new SqlDataAdapter();
            con = new SqlConnection(CONNECTIONSTRING);
            con.Open();
            tran = con.BeginTransaction();
            var cmd = new SqlCommand(sql, con, tran);
            sa.DeleteCommand = cmd;
            sa.DeleteCommand.ExecuteNonQuery();
            tran.Commit();
        }
        catch (Exception exp)
        {
            tran.Rollback();
            throw new Exception(exp.Message);
        }
        finally
        {
            con.Close();
        }
        return true;
    }

    public void ExecuteQuary(string Procedure)
    {
        string sql = "";
        sql = Procedure;
        ReadReader(sql);
    }

    public DataSet GetDataSet(string sqlStatement)
    {
        try
        {
            if (iReader != null)
            {
                iReader.Close();
            }
            iDataSet.Tables.Clear();
            Connect();
            var oDap = new SqlDataAdapter(sqlStatement, iConnection);
            //oDap.SelectCommand.CommandTimeout = 50000;

            oDap.Fill(iDataSet);
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        CloseConnection();
        return iDataSet;
    }


    public static String Reverse(String strParam)
    {
        if (strParam.Length == 1)
        {
            return strParam;
        }
        else
        {
            return Reverse(strParam.Substring(1)) + strParam.Substring(0, 1);
        }
    }

    public void PopulateDropDownListBox(DropDownList ddlBox, DataTable data_table, int col)
    {
        var hTable = new Hashtable();
        var duplicateList = new ArrayList();
        //Add list of all the unique item value to hashtable, which stores combination of key, value pair.   
        //And add duplicate item value in arraylist.   
        var paramDt = new DataTable();
        paramDt = data_table.Copy();

        foreach (DataRow drow in paramDt.Rows)
        {
            if (hTable.Contains(drow[col]))
                duplicateList.Add(drow);
            else
                hTable.Add(drow[col], string.Empty);
        }
        //Removing a list of duplicate items from datatable.   
        foreach (DataRow dRow in duplicateList)
            paramDt.Rows.Remove(dRow);
        //Datatable which contains unique records will be return as output.      

        for (int i = 0; i < paramDt.Rows.Count; i++)
        {
            if (paramDt.Rows[i][col].ToString() == "")
            {
                paramDt.Rows.Remove(paramDt.Rows[i]);
            }
        }


        if (paramDt.Rows.Count > 0)
        {
            DataRow iDtr = paramDt.NewRow();
            iDtr[col] = "Please Select(x)";
            paramDt.Rows.Add(iDtr);
        }
        ddlBox.Text = "";
        ddlBox.DataSource = paramDt;
        ddlBox.DataBind();
        ddlBox.DataValueField = paramDt.Columns[col].ToString();
        ddlBox.DataTextField = paramDt.Columns[col].ToString();


        //drpbox.DisplayMember = paramDt.Columns[col].ToString();
        if (ddlBox.Items.Count > 0)
            ddlBox.SelectedIndex = ddlBox.Items.Count - 1;
    }

    public string removeCode(string inputString)
    {
        string str = inputString;
        try
        {
            str = Reverse(str);
            int startIndex = str.IndexOf("(", 0) + 1;
            int length = str.Length - 1;
            if (length <= 0)
                return "";
            str = str.Substring(startIndex);
            str = Reverse(str);
        }
        catch (Exception iException)
        {
            //MessageBox.Show(iException.Message);
        }
        return str;
    }

    public string GetCode(string inputString)
    {
        string str = inputString;
        try
        {
            str = Reverse(str);
            int startIndex = str.IndexOf(")", 0) + 1;
            int length = str.IndexOf("(", 0) - startIndex;
            if (length <= 0)
                return "";
            str = str.Substring(startIndex, length);
            str = Reverse(str);
        }
        catch (Exception iException)
        {
            //MessageBox.Show(iException.Message);
        }
        return str;
    }
}